<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Bill_stats_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_account(){
      $this->db->where('entity_id',$this->session->userdata('entity_id'));
      $q = $this->db->get('ar_account');
      return $q->result();
    }

    public function get_status($id){
    	$sql = "SELECT *,aa.acct_name,aa.acct_num,aea.zone_1 FROM ubs_meter_readings umr
    			INNER JOIN ar_account aa ON aa.account_id = umr.account_id
    			INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr 
    			WHERE umr.bill_id = '".$id."' LIMIT 1";
    	$q = $this->db->query($sql);
    	return $q->result();
    }
//ap.account_id IN (SELECT account_id FROM ar_account WHERE acct_num = ".$num.")
    public function get_bal($id,$date1,$date2){
		$sql = "SELECT ap.payment_id,ap.invoice_total,ap.unpaid_amt,ap.pay_amount,ap.invoice_balance,ap.account_id,ap.invoice_id,ap.receipt_id,ar.receipt_date,(SELECT total_due FROM ubs_meter_readings WHERE bill_id = ".$id.") curchg FROM ar_payments ap
				INNER JOIN ar_receipt ar ON ap.receipt_id = ar.receipt_id
				WHERE ap.invoice_id IN (SELECT invoice_id FROM ubs_invoices WHERE invoice_num = ".$id.")
                AND ar.reciept_date BETWEEN str_to_date('".$date1."','%d-%b-%y') AND str_to_date('".$date2."','%d-%b-%y')";
    	$q = $this->db->query($sql);
    	return $q->result();
    }

    public function get_inv($id){
    	$sql = "SELECT uil.line_amt,uil.charge_code,uil.line_num,ui.invoice_id FROM ubs_invoices ui,ubs_invoice_lines uil
    			WHERE uil.invoice_id = ui.invoice_id
    			AND ui.invoice_num = ".$id;
    	$q = $this->db->query($sql);
    	return $q->result();
    }

    function get_bill($num){
    	$sql = "SELECT umr.reading_date,aa.acct_num,aa.acct_name,umr.account_id,umr.bill_id,umr.bill_year, umr.bill_month FROM ubs_meter_readings umr
    			INNER JOIN ar_account aa ON aa.account_id = umr.account_id
    			WHERE umr.account_id = '".$num."' ORDER BY umr.bill_id ASC";
    	$q = $this->db->query($sql);
    	return $q->result();
    }
}